package com.cz.modules.jxc.controller;

import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.cz.common.utils.POIExcelUtils;
import com.cz.common.validator.ValidatorUtils;
import com.cz.modules.cll.service.CllVehicleRecordService;
import com.cz.modules.gc.service.GcToiletService;
import com.cz.modules.jxc.entity.JxcShopEntity;
import com.cz.modules.jxc.service.JxcShopService;
import com.cz.modules.rll.entity.RllPlaceEntity;
import com.cz.modules.rll.service.RllHourCountService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cz.modules.jxc.entity.JxcSaleOrderEntity;
import com.cz.modules.jxc.service.JxcSaleOrderService;
import com.cz.common.utils.PageUtils;
import com.cz.common.utils.R;
import sun.font.FontRunIterator;

import javax.servlet.http.HttpServletResponse;


/**
 * 销售流水账
 *
 * @author Cz
 * @email 
 * @date 2020-04-16 13:42:40
 */
@RestController
@RequestMapping("jxc/jxcsaleorder")
public class JxcSaleOrderController {
    @Autowired
    private JxcSaleOrderService jxcSaleOrderService;
    @Autowired
    private JxcShopService jxcShopService;
    @Autowired
    private RllHourCountService rllHourCountService;
    @Autowired
    private CllVehicleRecordService cllVehicleRecordService;
    @Autowired
    private GcToiletService gcToiletService;

    /**
     * 导出
     */
    @RequestMapping("/upload")
    public void upload(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }

        List skdj=jxcSaleOrderService.southAndNorthAveragePriceDay(southShopList,startTime,endTime);//南区客单价 list.get(1)
        List nkdj=jxcSaleOrderService.southAndNorthAveragePriceDay(northShopList,startTime,endTime);//北区客单价

        List srjxf=jxcSaleOrderService.proportionOfConsumers("3",startTime,endTime);//南区人均消费占比
        List nrjxf=jxcSaleOrderService.proportionOfConsumers("4",startTime,endTime);//北区人均消费占比

        List southOrderSize=jxcSaleOrderService.orderSizelist(southShopList,startTime,endTime );//南区订单数
        List northOrderSize=jxcSaleOrderService.orderSizelist(northShopList,startTime,endTime); //北区订单数

        List sSaleCount= jxcSaleOrderService.orderCountlist(southShopList,startTime,endTime);//南区销售额
        List nSaleCount=jxcSaleOrderService.orderCountlist(northShopList,startTime,endTime);//北区销售额

        //商户销售统计
        List shopsale=jxcSaleOrderService.shopSaleCount(startTime,endTime);
        List<JxcSaleOrderEntity> orderList= (List) shopsale.get(0);

        List<String> shopName=(List) shopsale.get(2);

        List<Integer>  sizes=new ArrayList<>();
        List<Double>  counts=new ArrayList<>();
        List<String> timeList = (List<String>)skdj.get(0);
        List<Double> southkdj= (List<Double>) skdj.get(1);//南区客单价
        List<Double> northkdj= (List<Double>) nkdj.get(1);
        DecimalFormat df = new DecimalFormat("0.00");
        DecimalFormat df1 = new DecimalFormat("0");


        String fileName = "销售数据统计"+new Date().getTime()+".xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        /************style样式**********/
        HSSFCellStyle style = workbook.createCellStyle();//样式  标题用
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)22);//字体大小
        font.setBold(true);//加粗
        style.setFont(font);//将字体样式放入style
        font.setFontName("宋体");
        style.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中


        HSSFCellStyle style1 = workbook.createCellStyle();//样式1 第二行用
        HSSFFont font1 = workbook.createFont();
        font1.setFontHeightInPoints((short)12);//字体大小
        font1.setBold(false);//加粗
        font1.setFontName("宋体");
        style1.setFont(font1);//将字体样式放入style
        style1.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style1.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style1.setWrapText(true);//自动换行
        style1.setBorderBottom(BorderStyle.THIN); //下边框
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderTop(BorderStyle.THIN);//上边框
        style1.setBorderRight(BorderStyle.THIN);//右边框


        HSSFCellStyle style2 = workbook.createCellStyle();//样式2 带绿色背景颜色的
        style2.setFont(font1);//将字体样式放入style
        style2.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style2.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style2.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setWrapText(true);//自动换行
        style2.setBorderBottom(BorderStyle.THIN); //下边框
        style2.setBorderLeft(BorderStyle.THIN);//左边框
        style2.setBorderTop(BorderStyle.THIN);//上边框
        style2.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style3 = workbook.createCellStyle();//样式3 带红色背景颜色的 倒数第二行 合计用
        style3.setFont(font1);//将字体样式放入style
        style3.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style3.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style3.setFillForegroundColor(IndexedColors.RED.getIndex());
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
         style3.setBorderBottom(BorderStyle.THIN); //下边框
        style3.setBorderLeft(BorderStyle.THIN);//左边框
        style3.setBorderTop(BorderStyle.THIN);//上边框
        style3.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style4 = workbook.createCellStyle();//样式4 带蓝色背景颜色的 最后一行平均用
        style4.setFont(font1);//将字体样式放入style
        style4.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style4.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style4.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style4.setBorderBottom(BorderStyle.THIN); //下边框
        style4.setBorderLeft(BorderStyle.THIN);//左边框
        style4.setBorderTop(BorderStyle.THIN);//上边框
        style4.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style5 = workbook.createCellStyle();
        style5.setFont(font1);//将字体样式放入style
        style5.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style5.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style5.setWrapText(true);//自动换行
        style5.setBorderBottom(BorderStyle.THIN); //下边框
        style5.setBorderLeft(BorderStyle.THIN);//左边框
        style5.setBorderTop(BorderStyle.THIN);//上边框
        style5.setBorderRight(BorderStyle.THIN);//右边框


        HSSFCellStyle style6 = workbook.createCellStyle();//
        style6.setFont(font1);//将字体样式放入style
        style6.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style6.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style6.setFillForegroundColor(IndexedColors.RED.getIndex());
        style6.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style6.setBorderBottom(BorderStyle.THIN); //下边框
        style6.setBorderLeft(BorderStyle.THIN);//左边框
        style6.setBorderTop(BorderStyle.THIN);//上边框
        style6.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style7 = workbook.createCellStyle();//样式4 带蓝色背景颜色的
        style7.setFont(font1);//将字体样式放入style
        style7.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style7.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style7.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style7.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style7.setBorderBottom(BorderStyle.THIN); //下边框
        style7.setBorderLeft(BorderStyle.THIN);//左边框
        style7.setBorderTop(BorderStyle.THIN);//上边框
        style7.setBorderRight(BorderStyle.THIN);//右边框
        /************style样式**********/

        String str[]={"日期","南区销售额","北区销售额","总销售额","南区订单量","北区订单量","总订单量",
                "南区客单价","北区客单价","总客单价","南区消费人次占比 %","北区消费人次占比 %","两区消费人次占比 %",};
        HSSFSheet sheet = workbook.createSheet("销售统计");
       /*****************************sheet1 开始*******************************/
        HSSFSheet sheet1 = workbook.createSheet("商户销售明细");
        for (int i = 0; i <((shopName.size())*2)+1 ; i++) {
            sheet1.setColumnWidth(i, (int)40*100);//列宽 excel中1字符约等于 此处2.8
        }
        //sheet1 第一行
        HSSFRow sheetRow0 = sheet1.createRow(0);
        sheetRow0.setHeight((short) 800); // 设置行高
        HSSFCell sheetRow0cell0 = sheetRow0.createCell(0);
        sheet1.addMergedRegion(new CellRangeAddress(0,0,0,40));
        sheetRow0cell0.setCellValue(new HSSFRichTextString("商户销售明细"));
        sheetRow0cell0.setCellStyle(style);//将style放入cell
        //第二行
        HSSFRow sheetRow1 = sheet1.createRow(1);
        HSSFCell sheetRow1cell00 = sheetRow1.createCell(0);
        sheetRow1cell00.setCellValue(new HSSFRichTextString("日期"));
        sheetRow1.setHeight((short) 600); // 设置行高
        sheet1.addMergedRegion(new CellRangeAddress(1,2,0,0));
        sheetRow1cell00.setCellStyle(style5);
        for (int i = 0; i <shopName.size(); i++) {
            sheet1.addMergedRegion(new CellRangeAddress(1,1,(i*2)+1,(i*2)+2));
            HSSFCell sheetRow1cell01 = sheetRow1.createCell((i*2)+1);
            HSSFCell sheetRow1cell011 = sheetRow1.createCell((i*2)+2);
            sheetRow1cell01.setCellValue(new HSSFRichTextString((String) shopName.get(i)));
            sheetRow1cell01.setCellStyle(style2);//不要背景颜色用 style5 即可
            sheetRow1cell011.setCellStyle(style2);
        }
        //第三行
        HSSFRow sheetRow2 = sheet1.createRow(2);
        sheetRow2.setHeight((short) 500); // 设置行高
        for (int i = 1; i <(shopName.size()*2)+1 ; i++) {
            HSSFCell sheetRow2cell00 = sheetRow2.createCell(i);
            if((i%2) == 0){
                sheetRow2cell00.setCellValue(new HSSFRichTextString("销售额"));
            }else {
                sheetRow2cell00.setCellValue(new HSSFRichTextString("单数"));
            }
            sheetRow2cell00.setCellStyle(style5);
        }
        //内容
        for (int i = 0; i <timeList.size() ; i++) {
            HSSFRow sheetRowt3 = sheet1.createRow(i+3);//行
            sheetRowt3.setHeight((short) 400); // 设置行高
            HSSFCell sheetRowt3cell00 = sheetRowt3.createCell(0);
            sheetRowt3cell00.setCellValue(new HSSFRichTextString(timeList.get(i)));
            sheetRowt3cell00.setCellStyle(style5);
            for (int j = 0; j <shopName.size(); j++) {
                HSSFCell sheetRowt3cell01 = sheetRowt3.createCell((j*2)+1);
                HSSFCell sheetRowt3cell02 = sheetRowt3.createCell((j*2)+2);
                Integer size=0;
                Double count=0D;
                for (int k = 0; k <orderList.size() ; k++) {
                    if(orderList.get(k).getName().equals(timeList.get(i)) && orderList.get(k).getShopName().equals(shopName.get(j))){
                        size ++;
                        count +=orderList.get(k).getTotalAmount();
                    }
                }
                sheetRowt3cell01.setCellValue(size);
                sheetRowt3cell02.setCellValue(Double.parseDouble(df.format(count)));
                sheetRowt3cell01.setCellStyle(style5);
                sheetRowt3cell02.setCellStyle(style5);
                sizes.add(size);
                counts.add(Double.parseDouble(df.format(count)));
            }
        }

        Map<String,List> map=new HashMap<>();
        for (String shopname:shopName) {
            int size=0;
            Double count=0D;
            for (JxcSaleOrderEntity order:orderList) {
                if(order.getShopName().equals(shopname)){
                    size ++;
                    count +=order.getTotalAmount();
                }
            }
            List alist=new ArrayList();
            alist.add(size);
            alist.add(count);
            map.put(shopname,alist);
        }
        //倒数第二行 总计
        HSSFRow rowlast2 = sheet1.createRow(timeList.size()+3);
        HSSFCell rowlast2cell00 = rowlast2.createCell(0);
        rowlast2cell00.setCellValue(new HSSFRichTextString("总计"));
        rowlast2cell00.setCellStyle(style6);
            for (int j = 0; j <shopName.size(); j++) {
                HSSFCell rowlast2cell01 = rowlast2.createCell((j*2)+1);
                HSSFCell rowlast2cell02 = rowlast2.createCell((j*2)+2);

                rowlast2cell02.setCellValue(df.format(map.get(shopName.get(j)).get(1)));
                rowlast2cell01.setCellValue(df.format(map.get(shopName.get(j)).get(0)));

                rowlast2cell01.setCellStyle(style6);
                rowlast2cell02.setCellStyle(style6);
            }

        //倒数第一行 平均
        HSSFRow rowlast11 = sheet1.createRow(timeList.size()+4);
        HSSFCell rowlast11cell00 = rowlast11.createCell(0);
        rowlast11cell00.setCellValue(new HSSFRichTextString("平均"));
        rowlast11cell00.setCellStyle(style7);
        for (int j = 0; j <shopName.size(); j++) {
            HSSFCell rowlast2cell01 = rowlast11.createCell((j*2)+1);
            HSSFCell rowlast2cell02 = rowlast11.createCell((j*2)+2);

            Double acount=  (Double) map.get(shopName.get(j)).get(1);
            Integer asize=  (Integer) map.get(shopName.get(j)).get(0);

            String zgacount = df.format((Double) acount/timeList.size());
            String zgasize = df1.format((Integer) asize/timeList.size());

            rowlast2cell01.setCellValue(zgasize);
            rowlast2cell02.setCellValue(zgacount);

            rowlast2cell01.setCellStyle(style7);
            rowlast2cell02.setCellStyle(style7);
        }
        /*****************************sheet1 结束*******************************/
        /*****************************sheet 开始***********************************/

         for (int i = 0; i <str.length ; i++) {
            sheet.setColumnWidth(i, (int)30*100);//列宽 excel中1字符约等于 此处2.8
        }
        // sheet.setColumnWidth(0, (int)19.3*100);//列宽 excel中1字符约等于 此处2.8
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,12));//合并单元格 第一行标题

         //创建Excel工作表第一行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);
        row0.setHeight((short) 800);//行高 1磅=20short
        cell0.setCellValue(new HSSFRichTextString("盂县服务区数据统计"));
        cell0.setCellStyle(style);//将style放入cell


        //第二行
        HSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell1 = row1.createCell(i);
            row1.setHeight((short) 900); // 设置行高
            cell1.setCellValue(new HSSFRichTextString(str[i]));
            if(i == 3 || i == 6 || i == 9 || i == 12 || i == 15 || i==18 || i==21){
                cell1.setCellStyle(style2);//将style放入cell
            }else {
                cell1.setCellStyle(style1);//将style放入cell
            }
        }
        Double totalkdj=0D;
        Double totalkdjs=0D;
        Double totalkdjn=0D;
        Double totalordercounts=0D;
        Double totalordercountn=0D;
        Double totalordercount=0D;
        int totalordersizes=0;
        int totalordersizen=0;
        int totalordersize=0;
        Double totalrzxf=0D;
        Double totalrzxfs=0D;
        Double totalrzxfn=0D;
        //创建内容所有行
        for (int i = 0; i <timeList.size() ; i++) {
            HSSFRow row= sheet.createRow(i+2);//创建行
            row.setHeight((short) 600); // 设置行高
            for (int j = 0; j <str.length ; j++) {
                HSSFCell cell = row.createCell(j);//创建单元格
                if(j ==0){//日期
                    cell.setCellValue(timeList.get(i));
                }else if(j ==1){//销售额
                    totalordercounts +=(Double)sSaleCount.get(i);
                    cell.setCellValue(df.format(sSaleCount.get(i)));
                }else if(j ==2){
                    totalordercountn +=(Double)nSaleCount.get(i);
                    cell.setCellValue(df.format(nSaleCount.get(i)));
                }else if(j ==3){
                    Double ssale=(Double) sSaleCount.get(i);
                    Double nsale=(Double) nSaleCount.get(i);
                    Double tsale=(Double)(ssale+nsale);
                    totalordercount += (Double.parseDouble(df.format(tsale)));
                    cell.setCellValue(df.format(tsale));
                }else if(j ==4){//订单数量
                    totalordersizes +=(Integer) southOrderSize.get(i);
                    cell.setCellValue(df.format(southOrderSize.get(i)));
                }else if(j ==5){
                    totalordersizen +=(Integer) northOrderSize.get(i);
                    cell.setCellValue(df.format(northOrderSize.get(i)));
                }else if(j ==6){
                    totalordersize +=((Integer)southOrderSize.get(i)+(Integer)northOrderSize.get(i));
                    cell.setCellValue(df.format((Integer)southOrderSize.get(i)+(Integer)northOrderSize.get(i)));
                }else if(j ==7){//客单价
                    totalkdjs +=southkdj.get(i);
                    cell.setCellValue(df.format(southkdj.get(i)));
                }else if(j ==8){
                    totalkdjn +=northkdj.get(i);
                    cell.setCellValue(df.format(northkdj.get(i)));
                }else if(j ==9){
                    Double sk=(Double)southkdj.get(i);
                    Double nk=(Double)northkdj.get(i);
                    Double tk=(Double)(sk+nk);
                    totalkdj +=((Double) tk/2);
                    cell.setCellValue(df.format((Double) tk/2));
                }else if(j ==10){//人均消费占比
                    totalrzxfs +=(Double) (srjxf.get(i));
                    cell.setCellValue(df.format((srjxf.get(i))));//+"%"
                }else if(j ==11){
                    totalrzxfn +=(Double) nrjxf.get(i);
                    cell.setCellValue(df.format(nrjxf.get(i)));//+"%"
                }else if(j ==12){
                    Double d=((Double)srjxf.get(i)+(Double)nrjxf.get(i))/2;
                    totalrzxf +=(Double.parseDouble(df.format(d)));
                    cell.setCellValue(df.format(d));
                }
                cell.setCellStyle(style1);
            }
        }
        //总计
        HSSFRow rowLast2= sheet.createRow(timeList.size()+2);//创建行
        rowLast2.setHeight((short) 600); // 设置行高
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell = rowLast2.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("合计");
            }else if(i == 1){
                cell.setCellValue(df.format(totalordercounts));
            }else if(i == 2){
                cell.setCellValue(df.format(totalordercountn));
            }else if(i == 3){
                cell.setCellValue(df.format(totalordercount));
            }else if(i == 4){
                cell.setCellValue(df.format(totalordersizes));
            }else if(i == 5){
                cell.setCellValue(df.format(totalordersizen));
            }else if(i == 6){
                cell.setCellValue(df.format(totalordersize));
            }else if(i == 7){
                cell.setCellValue(df.format(totalkdjs));
            }else if(i == 8){
                cell.setCellValue(df.format(totalkdjn));
            }else if(i == 9){
                cell.setCellValue(df.format(totalkdj));
            }else if(i == 10){
                cell.setCellValue(df.format(totalrzxfs));
            }else if(i == 11){
                cell.setCellValue(df.format(totalrzxfn));
            }else if(i == 12){
                cell.setCellValue(df.format(totalrzxf));
            }
            cell.setCellStyle(style3);
        }
        //平均
        HSSFRow rowLast= sheet.createRow(timeList.size()+3);//创建行
        rowLast.setHeight((short) 600); // 设置行高
        int size=timeList.size();
        if(size ==0){
            size =1;
        }
        for (int i = 0; i <str.length ; i++){
            HSSFCell cell = rowLast.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("平均");
            }else if(i == 1){
                String kgratio = df.format((Double) totalordercounts/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 2){
                String kgratio = df.format((Double) totalordercountn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 3){
                String kgratio = df.format((Double) totalordercount/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 4){
                String kgratio = df1.format((float) totalordersizes/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 5){
                String kgratio = df1.format((float) totalordersizen/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 6){
                String kgratio = df1.format((float) totalordersize/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 7){
                String kgratio = df.format((Double) totalkdjs/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 8){
                String kgratio = df.format((Double) totalkdjn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 9){
                String kgratio = df.format((Double) totalkdj/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 10){
                String kgratio = df.format((Double) totalrzxfs/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 11){
                String kgratio = df.format((Double) totalrzxfn/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }else if(i == 12){
                String kgratio = df.format((Double) totalrzxf/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(kgratio);
            }
            cell.setCellStyle(style4);
        }
        /*****************************sheet 结束***********************************/
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 列表
     */
    @RequestMapping("/list")
    @RequiresPermissions("jxc:jxcsaleorder:list")
    public R list(@RequestParam Map<String, Object> params){
        PageUtils page = jxcSaleOrderService.queryPage(params);

        return R.ok().put("page", page);
    }


    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
    @RequiresPermissions("jxc:jxcsaleorder:info")
    public R info(@PathVariable("id") String id){
        JxcSaleOrderEntity jxcSaleOrder = jxcSaleOrderService.getById(id);

        return R.ok().put("jxcSaleOrder", jxcSaleOrder);
    }

    /**
     * 保存
     */
    @RequestMapping("/save")
    @RequiresPermissions("jxc:jxcsaleorder:save")
    public R save(@RequestBody JxcSaleOrderEntity jxcSaleOrder){
        jxcSaleOrderService.save(jxcSaleOrder);

        return R.ok();
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    @RequiresPermissions("jxc:jxcsaleorder:update")
    public R update(@RequestBody JxcSaleOrderEntity jxcSaleOrder){
        ValidatorUtils.validateEntity(jxcSaleOrder);
        jxcSaleOrderService.updateById(jxcSaleOrder);
        
        return R.ok();
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    @RequiresPermissions("jxc:jxcsaleorder:delete")
    public R delete(@RequestBody String[] ids){
        jxcSaleOrderService.removeByIds(Arrays.asList(ids));

        return R.ok();
    }
    /**
     * 今日销售量/销售
     */
    @RequestMapping("/xsTodayCount")
    public R xsTodayCount(){
        String today =new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        Integer xslTodayCount=jxcSaleOrderService.count(
                new QueryWrapper<JxcSaleOrderEntity>().ge("sell_master_date",today+" 00:00:00")
        );
        Integer xse=0;
        JxcSaleOrderEntity order=jxcSaleOrderService.xseTodayCount(today +" 00:00:00");
        if(order!=null){
            Double totalAmount=order.getTotalAmount();
            if(totalAmount==null){
                totalAmount=0D;
            }
            xse=new Double(totalAmount).intValue();
        }
        return R.ok().put("xslTodayCount",String.valueOf(xslTodayCount).toCharArray()).put("xseTodayCount",String.valueOf(xse).toCharArray());
    }
    /**
     * 24小时销售趋势
     */
    @RequestMapping("/hour24sale")
    public R hour24sale(){
        return R.ok().put("southsale",jxcSaleOrderService.hour24Sale("3")).put("northsale",jxcSaleOrderService.hour24Sale("4"));
    }
    /**
     * 销售总额
     */
    @RequestMapping("/sailCount")
    public R sailCount(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                String da=DateUtil.format(new Date(),"yyyy-MM-dd");
                startTime=da+ " 00:00:00";
                endTime=da+" 23:59:59";
            }else if("1".equals(type)){//当月的数据
                startTime= DateUtil.format(new Date(),"yyyy-MM");
                startTime +="-01 00:00:00";
                // 获取当前月的最后一天
                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(new Date());
                calendar1.add(Calendar.MONTH, 1);
                calendar1.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar1.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }
        //分别查询南区北区销售额
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<String> southShopList=new ArrayList<>();
        List<String> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if("3".equals(shop.getDeptId())){
                southShopList.add(shop.getShopCode());
            }else if("4".equals(shop.getDeptId())){
                northShopList.add(shop.getShopCode());
            }
        }
        JxcSaleOrderEntity southSaleOrder=jxcSaleOrderService.xseCount(startTime,endTime,southShopList);
        JxcSaleOrderEntity northSaleOrder=jxcSaleOrderService.xseCount(startTime,endTime,northShopList);
        Double southCount=southSaleOrder.getTotalAmount();
        Double northCount=northSaleOrder.getTotalAmount();

        return R.ok().put("southCount",southCount.intValue()).put("northCount",northCount.intValue());
    }
    /**
     * 销售趋势
     */
    @RequestMapping("/salebytime")
    public R salebytime(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        Date date=new Date();
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                endTime=DateUtil.format(date,"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime = DateUtil.format(date,"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }
        if("0".equals(type)){//小时
            return R.ok()
                    .put("southsale",jxcSaleOrderService.salehour("3",startTime,endTime)).put("northsale",jxcSaleOrderService.salehour("4",startTime,endTime));

        }else if("1".equals(type)){//天
            return R.ok()
                    .put("southsale",jxcSaleOrderService.saleday("3",startTime,endTime)).put("northsale",jxcSaleOrderService.saleday("4",startTime,endTime));

        }
        return R.ok();
    }
    /**
     * 今日销售趋势
     */
    @RequestMapping("/hourTodaysale/{type}")
    public R hourTodaysale(@PathVariable("type") String type){
        return R.ok().put("southsale",jxcSaleOrderService.hourTodaySale("3",type)).put("northsale",jxcSaleOrderService.hourTodaySale("4",type));
    }
    /**
     * 南北区24小时销量统计
     */
    @RequestMapping("/hour24Salesvolume")
    public R hour24Salesvolume(){
        return R.ok().put("southvolume",jxcSaleOrderService.hour24Salesvolume("3")).put("northvolume",jxcSaleOrderService.hour24Salesvolume("4"));
    }
    /**
     * 南北区今日销量统计
     */
    @RequestMapping("/hourTodaySalesvolume/{type}")
    public R hourTodaySalesvolume(@PathVariable("type") String type){
        //当天
        String date= DateUtil.format(new Date(),"yyyy-MM-dd");
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<String> southShopList=new ArrayList<>();
        List<String> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if("3".equals(shop.getDeptId())){
                southShopList.add(shop.getShopCode());
            }else if("4".equals(shop.getDeptId())){
                northShopList.add(shop.getShopCode());
            }
        }
        //南区商户编码
        List<JxcSaleOrderEntity> southList=jxcSaleOrderService.xlChart(date+" 00:00:00",date+" 23:59:59",southShopList);
        List<JxcSaleOrderEntity> northList=jxcSaleOrderService.xlChart(date+" 00:00:00",date+" 23:59:59",northShopList);
        return R.ok().put("southvolume",southList).put("northvolume",northList);
    }
    /**
     * 南北区销量统计 第三页面
     */
    @RequestMapping("/orderbytime")
    public R orderbytime(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<String> southShopList=new ArrayList<>();
        List<String> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if("3".equals(shop.getDeptId())){
                southShopList.add(shop.getShopCode());
            }else if("4".equals(shop.getDeptId())){
                northShopList.add(shop.getShopCode());
            }
        }
        Date date=new Date();
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.HOUR_OF_DAY, 1);//1小时后
                calendar.set(Calendar.MINUTE, 0);
                calendar.set(Calendar.SECOND, 0);
                calendar.set(Calendar.MILLISECOND, 0);
                endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                // 获取当前月的最后一天
                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(date);
                calendar1.add(Calendar.MONTH, 1);
                calendar1.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar1.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }
        //南区商户编码
        List<JxcSaleOrderEntity> southList=jxcSaleOrderService.xlChart(startTime,endTime,southShopList);
        List<JxcSaleOrderEntity> northList=jxcSaleOrderService.xlChart(startTime,endTime,northShopList);
        return R.ok().put("southvolume",southList).put("northvolume",northList);
    }
    /**
     * 客流 销售额
     */
    @RequestMapping("/salerll")
    public R salerll(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        Date date=new Date();
        String startTime="";
        String endTime="";
        Map<String,List>  salecllrlllist=null;
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
                salecllrlllist=jxcSaleOrderService.salerllhour(startTime,endTime);
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
                salecllrlllist=jxcSaleOrderService.salerllday(startTime,endTime);
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                endTime=DateUtil.format(date,"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
                salecllrlllist=jxcSaleOrderService.salerllhour(startTime,endTime);


                startTime= DateUtil.format(new Date(),"yyyy-MM-dd");
                startTime+=" 00:00:00";
                endTime=DateUtil.format(new Date(),"yyyy-MM-dd HH");
                endTime+=":00:00";
                List slist=rllHourCountService.kllChart1("南区",startTime,endTime);
                List nlist=rllHourCountService.kllChart1("北区",startTime,endTime);

                List<Integer> ssList= (List<Integer>) slist.get(1);
                List<Integer> nnList= (List<Integer>) nlist.get(1);

                List<Integer> kllList1=new ArrayList<>();
                for (int i = 0; i <ssList.size() ; i++) {
                    kllList1.add(ssList.get(i)+nnList.get(i));
                }
                salecllrlllist.put("客流量",kllList1);

            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime = DateUtil.format(date,"yyyy-MM-dd");
                endTime +=" 23:59:59";
                salecllrlllist=jxcSaleOrderService.salerllday(startTime,endTime);
            }
        }
        return R.ok().put("salecllrlllist",salecllrlllist);
    }
    /**
     * 客流 车流 销售额
     */
    @RequestMapping("/salecllrll")
    public R salecllrll(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        Date date=new Date();
        String startTime="";
        String endTime="";
        Map<String,List>  salecllrlllist=null;
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
                salecllrlllist=jxcSaleOrderService.salecllrllhour(startTime,endTime);
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
                salecllrlllist=jxcSaleOrderService.salecllrllday(startTime,endTime);
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                endTime=DateUtil.format(date,"yyyy-MM-dd HH:mm:ss");
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
                salecllrlllist=jxcSaleOrderService.salecllrllhour(startTime,endTime);


                startTime= DateUtil.format(new Date(),"yyyy-MM-dd");
                startTime+=" 00:00:00";
                endTime=DateUtil.format(new Date(),"yyyy-MM-dd HH");
                endTime+=":00:00";
                List slist=rllHourCountService.kllChart1("南区",startTime,endTime);
                List nlist=rllHourCountService.kllChart1("北区",startTime,endTime);

                List<Integer> ssList= (List<Integer>) slist.get(1);
                List<Integer> nnList= (List<Integer>) nlist.get(1);

                List<Integer> kllList1=new ArrayList<>();
                for (int i = 0; i <ssList.size() ; i++) {
                    kllList1.add(ssList.get(i)+nnList.get(i));
                }
                salecllrlllist.put("客流量",kllList1);

            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime = DateUtil.format(date,"yyyy-MM-dd");
                endTime +=" 23:59:59";
                salecllrlllist=jxcSaleOrderService.salecllrllday(startTime,endTime);
            }
        }
        return R.ok().put("salecllrlllist",salecllrlllist);
    }
    /**
     * 客单价
     */
    @RequestMapping("/averagePrice")
    public R averagePrice(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String time= (String) params.get("time");
        Date date=new Date();
        String startTime="";
        String endTime="";
        if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                calendar.add(Calendar.HOUR_OF_DAY, -1);//1小时前
                endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd HH");
                endTime +=":59:59";
                startTime=DateUtil.format(date,"yyyy-MM-dd");
                startTime +=" 00:00:00";
            }else if("1".equals(type)){//当月的数据
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                // 获取前月的第一天
                calendar.add(Calendar.MONTH, 0);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                startTime= DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime = DateUtil.format(date,"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
        }
        if("0".equals(type)){//小时
            return R.ok()
                    .put("averagePricelist",jxcSaleOrderService.averagePricehour(startTime,endTime));

        }else if("1".equals(type)){//天
            return R.ok()
                    .put("averagePricelist",jxcSaleOrderService.averagePriceday(startTime,endTime));

        }
        return R.ok();
    }
    /**
     * 列表
     */
    @RequestMapping("/orderNow")
    public R orderNow(){

        return R.ok().put("page", null);
    }
    /**
     * 列表
     */
    @RequestMapping("/detailist")
    public R detailist(){
        return R.ok().put("detail", jxcSaleOrderService.detailist());
    }
    /**
     * 实时订单列表
     */
    @RequestMapping("/realtimeorder")
    public R realtimeorder(){
        return R.ok().put("realtimeorder", jxcSaleOrderService.realtimeorder());
    }/**
     * 实时订单列表
     */
    @RequestMapping("/rllandprice")
    public R rllandprice(){
        return R.ok().put("rllandprice", jxcSaleOrderService.rllandprice());
    }

    /**
     * 商户销售统计
     */
    @RequestMapping("/shopSaleCount")
    public R shopSaleCount(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
       List list= jxcSaleOrderService.shopSaleCount(startTime,endTime);
       return R.ok().put("list",list);
    }
    /**
     * 销售额统计
     */
    @RequestMapping("/orderCount")
    public R orderCount(){
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
               northShopList.add(shop);
            }
        }
        //今日
        String today=DateUtil.format(new Date(),"yyyy-MM-dd");
        Double southToday=jxcSaleOrderService.orderCount(southShopList,today+" 00:00:00",today +" 23:59:59");
        Double northToday=jxcSaleOrderService.orderCount(northShopList,today+" 00:00:00",today +" 23:59:59");
        //昨日yesterday
        Calendar calendar=Calendar.getInstance();
        calendar.add(Calendar.DAY_OF_MONTH,-1);
        String yesterday=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        Double southYesterday=jxcSaleOrderService.orderCount(southShopList,yesterday+" 00:00:00",yesterday +" 23:59:59");
        Double northYesterday=jxcSaleOrderService.orderCount(northShopList,yesterday+" 00:00:00",yesterday +" 23:59:59");
        //本月
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_MONTH, 1);
        String mouthFirst=DateUtil.format(cal.getTime(),"yyyy-MM-dd");
        cal.set(Calendar.DATE, 1);    //主要就是这个roll方法
        cal.roll(Calendar.DATE, -1);
        String mouthLast=DateUtil.format(cal.getTime(),"yyyy-MM-dd");
        Double southMonth=jxcSaleOrderService.orderCount(southShopList,mouthFirst+" 00:00:00",mouthLast +" 23:59:59");
        Double northMonth=jxcSaleOrderService.orderCount(northShopList,mouthFirst+" 00:00:00",mouthLast +" 23:59:59");
        //今日订单数
        Integer southSize=jxcSaleOrderService.orderSize(southShopList,today+" 00:00:00",today +" 23:59:59");
        Integer northSize=jxcSaleOrderService.orderSize(northShopList,today+" 00:00:00",today +" 23:59:59");

        //客单价
        Double southKdj=0D;
        Double northKdj=0D;
        if(southSize!=0){
            southKdj=southToday/southSize;
        }
        if(northSize!=0){
            northKdj=northToday/northSize;
        }
        return R.ok().put("southToday",southToday).put("northToday",northToday).put("southYesterday",southYesterday).put("northYesterday",northYesterday).put("southMonth",southMonth).put("northMonth",northMonth).put("southKdj",southKdj).put("northKdj",northKdj);
    }

    /**
     * 销售额趋势
     */
    @RequestMapping("/orderChart")
    public R orderChart(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String startTime= (String) params.get("startTime");
        String endTime= (String) params.get("endTime");
        Calendar calendar = Calendar.getInstance();
        if("0".equals(type)){ //近7天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -6);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }else if("1".equals(type)){//近30天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -29);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }

        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }
        List southList=jxcSaleOrderService.orderChart(southShopList,startTime,endTime);
        List northList=jxcSaleOrderService.orderChart(northShopList,startTime,endTime);

        return R.ok().put("southList",southList).put("northList",northList);
    }

    /**
     * 销售额趋势
     */
    @RequestMapping("/shopChart")
    public R shopChart(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String startTime= (String) params.get("startTime");
        String endTime= (String) params.get("endTime");
        Calendar calendar = Calendar.getInstance();
        if("0".equals(type)){ //近7天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -6);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }else if("1".equals(type)){//近30天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -29);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }

        List<JxcShopEntity> shopList=jxcShopService.list();
        List<String> southShopList=new ArrayList<>();
        List<String> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop.getShopCode());
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop.getShopCode());
            }
        }
        //南区商户编码
        List<JxcSaleOrderEntity> southList=jxcSaleOrderService.xlChart(startTime,endTime,southShopList);
        List<JxcSaleOrderEntity> northList=jxcSaleOrderService.xlChart(startTime,endTime,northShopList);

        return R.ok().put("southList",southList).put("northList",northList);
    }


    /**
     * 页面echart数据导出
     */
    @RequestMapping("/exportSale")
    public void exportSale(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String type= (String) params.get("type");
        String startTime= (String) params.get("startTime");
        String endTime= (String) params.get("endTime");
        Calendar calendar = Calendar.getInstance();
        if("0".equals(type)){ //近7天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -6);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }else if("1".equals(type)){//近30天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -29);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }
        List southList=jxcSaleOrderService.orderChart(southShopList,startTime,endTime);
        List northList=jxcSaleOrderService.orderChart(northShopList,startTime,endTime);

        java.text.DecimalFormat df=new java.text.DecimalFormat("#0.00");
        //创建excel导出
        String[] title = {"日期","南区销售额","北区销售额","销售总额","日均销售额"};
        // excel文件名
        String fileName = "销售额统计"+new Date().getTime()+".xls";
        // sheet名
        String sheetName = "销售额统计";
        List<String> timeList = (List<String>)southList.get(0);
        List<String> sList = (List<String>)southList.get(1);
        List<String> nList = (List<String>)northList.get(1);
        String [][] content = new String[timeList.size()][5];
        for(int i=0;i<timeList.size();i++){
            content[i][0] = timeList.get(i);
            content[i][1] = sList.get(i);
            content[i][2] = nList.get(i);
            content[i][3] = String.valueOf(df.format(Double.parseDouble(sList.get(i)) + Double.parseDouble(nList.get(i))));
        }
        Double zongrll = 0D;
        for(String inte:sList){
            zongrll += Double.parseDouble(inte);
        }
        for(String inte:nList){
            zongrll += Double.parseDouble(inte);
        }
        content[0][4] = df.format(zongrll/timeList.size())+"元/天";
        // 创建HSSFWorkbook
        HSSFWorkbook wb = POIExcelUtils.getHSSFWorkbook(sheetName, title,content, null);
        // 响应到客户端
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            wb.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
